using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
using System.Text;
/*
 * Autor      : IT-Developer1
 * Data       : 02/04/2010 11:03:48
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
    public class CMedidasIndicadores
    {
        private string IDT_IND;
        private string IDT_TPO_MED;

        /// <sumary>
        /// Construtor da classe medidas_indicadores
        /// <sumary>
        public CMedidasIndicadores()
        {

        }

        /// <sumary>
        /// Construtor da classe Medidas_indicadores
        /// <sumary>
        public CMedidasIndicadores(string IDT_IND, string IDT_TPO_MED)
        {
            this.IDT_IND = IDT_IND;
            this.IDT_TPO_MED = IDT_TPO_MED;
        }

        #region Propriedades
        /// <sumary>
        /// Identificador do indicador
        /// <sumary>
        public string Idt_ind
        {
            get
            {
                return this.IDT_IND;
            }
            set
            {
                this.IDT_IND = value;
            }
        }
        /// <sumary>
        /// Identificador do tipo de medida
        /// <sumary>
        public string Idt_tpo_med
        {
            get
            {
                return this.IDT_TPO_MED;
            }
            set
            {
                this.IDT_TPO_MED = value;
            }
        }
        #endregion Propriedades


        public static void MedidaIndicador(string idt_ind, string Login, out string medida, out string medida2, out string medida3)
        {
            string sql = @"select tmedida.Dsc_tpo_Med from  Srx_Indicadores ind
  join srx_medidas_indicadores medida on ind.idt_ind = medida.idt_ind
  join srx_tipos_medidas tmedida on medida.idt_tpo_med = tmedida.idt_tpo_med
    where ind.IDT_IND = :IND
order by ORDEM_MIR";
            OracleCommand command = new OracleCommand();

            command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
            command.CommandText = sql;
            command.Parameters.Add(new OracleParameter("IND", OracleType.Number));
            command.Parameters["IND"].Value = idt_ind;

            DataTable tbl = new DataTable();
            tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

            medida = "VINCULAR";
            medida2 = "VINCULAR";
            medida3 = "VINCULAR";
            for (int i = 0; i < 3; i++)
            {
                if(tbl.Rows.Count-1 < i)
                    break;
                if (i == 0)
                    medida = tbl.Rows[i]["Dsc_tpo_Med"].ToString();
                if (i == 1)
                    medida2 = tbl.Rows[i]["Dsc_tpo_Med"].ToString();
                if (i == 2)
                    medida3 = tbl.Rows[i]["Dsc_tpo_Med"].ToString();
            }
        }

        public static void MedidasEmLote(string idt_ind, Dictionary<string, string> idts_tpo_med, string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL_DELETE = @"DELETE FROM Serix.SRX_MEDIDAS_INDICADORES MI
                                   WHERE IDT_IND = :IND";

            string SQL_INSERT = @"INSERT INTO Serix.SRX_MEDIDAS_INDICADORES 
                                   (IDT_TPO_MED, IDT_IND, ordem_mir) VALUES (:TPO_MED, :IND, :ordem_mir)";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;

                command.CommandText = SQL_DELETE;
                command.Parameters.Add(new OracleParameter("IND", OracleType.Number));
                command.Parameters["IND"].Value = idt_ind;

                command.ExecuteNonQuery();
                command.Parameters.Clear();

                command.CommandText = SQL_INSERT;
                command.Parameters.Add(new OracleParameter("IND", OracleType.Number));
                command.Parameters["IND"].Value = idt_ind;
                command.Parameters.Add(new OracleParameter("TPO_MED", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ordem_mir", OracleType.Number));
                foreach (string tpo_med in idts_tpo_med.Keys)
                {
                    command.Parameters["TPO_MED"].Value = tpo_med;
                    command.Parameters["ordem_mir"].Value = idts_tpo_med[tpo_med];
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Inserindo um novo Medidas_indicadores.
        /// </summary>
        public void Inserir(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_MEDIDAS_INDICADORES
			                  ( IDT_IND, IDT_TPO_MED )
						   values
						      ( :IDT_IND, :IDT_TPO_MED )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_IND", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED", OracleType.Number));

                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Alterando um Medidas_indicadores.
        /// </summary>
        public void Alterar(string Login)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_MEDIDAS_INDICADORES set 
			                   
						   where
						       IDT_IND = :IDT_IND, IDT_TPO_MED = :IDT_TPO_MED ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_IND", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED", OracleType.Number));

                command.Parameters["IDT_IND"].Value = this.IDT_IND;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        /// <summary>
        /// Exclui um  Medidas_indicadores.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_MEDIDAS_INDICADORES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_IND", DbType.Int32, this.IDT_IND));
            tab.adicionarFiltro(new Coluna("IDT_TPO_MED", DbType.Int32, this.IDT_TPO_MED));
            tab.delete();
        }

        static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_MEDIDAS_INDICADORES order by IDT_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public CMedidasIndicadores PesquisaporCodigo(string idt_ind, string idt_tpo_med, string login)
        {
            CMedidasIndicadores obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_MEDIDAS_INDICADORES 
                            WHERE IDT_IND = :IDT_IND, IDT_TPO_MED = :IDT_TPO_MED";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro(":IDT_IND", DbType.String, idt_ind);
                qy.AdicionarParametro(":IDT_TPO_MED", DbType.String, idt_tpo_med);

                qy.Executar();
                obj = new CMedidasIndicadores(qy.ObterCampo("IDT_IND"), qy.ObterCampo("IDT_TPO_MED"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CMedidasIndicadores();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_INDIDT_TPO_MED() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
                qy.Executar();
                return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return -1;
            }
            finally
            {
                qy.FecharConexao();
            }
        }

        static public DataTable ObterPorIDT_IND(string idt_ind)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_MEDIDAS_INDICADORES  where IDT_IND = :IDT_IND order by IDT_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_IND", OracleType.Number));
                command.Parameters["IDT_IND"].Value = idt_ind;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
        static public DataTable ObterPorIDT_TPO_MED(string idt_tpo_med)
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_MEDIDAS_INDICADORES  where IDT_TPO_MED = :IDT_TPO_MED order by IDT_IND ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED", OracleType.Number));
                command.Parameters["IDT_TPO_MED"].Value = idt_tpo_med;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
    }
}

